package com.harveyh.cemax.manager;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.harveyh.cemax.bean.LastReadInfoBean;
import com.harveyh.cemax.bean.ShiTiBean;
import com.harveyh.cemax.common.GloableData;

public class DBManager {
	
	static class BaseShiTi {
		public static final String KEY_locid = "locid",KEY_id = "id", KEY_jibie = "jibie", 
				KEY_type = "type", KEY_timu = "timu", KEY_A = "A", KEY_B = "B",
				KEY_C = "C", KEY_D = "D", KEY_daan = "daan", KEY_daan_desc = "daan_desc",
				KEY_tupian = "tupian", KEY_dui = "dui", KEY_cuo = "cuo", KEY_cuoti = "cuoti";

		public static final String[] columns = new String[] { KEY_locid,KEY_id,KEY_jibie, 
				KEY_type, KEY_timu,KEY_A, KEY_B,
				KEY_C , KEY_D , KEY_daan , KEY_daan_desc,
				KEY_tupian, KEY_dui, KEY_cuo, KEY_cuoti};
	}

	static class Chuji extends BaseShiTi{
		public static final String TableName = GloableData.chujiTableName;
		public static final String Create_table = "CREATE TABLE " + TableName
				+ "(locid INTEGER PRIMARY KEY AUTOINCREMENT, " 
				+ KEY_id + " INTEGER, "
				+ KEY_jibie + " TEXT, "
				+ KEY_type + " TEXT, "
				+ KEY_timu + " TEXT, "
				+ KEY_A + " TEXT, "
				+ KEY_B + " TEXT, "
				+ KEY_C + " TEXT, "
				+ KEY_D + " TEXT, "
				+ KEY_daan + " TEXT, "
				+ KEY_daan_desc + " TEXT, "
				+ KEY_tupian + " TEXT, "
				+ KEY_dui + " INTEGER, "
				+ KEY_cuo + " INTEGER, "
				+ KEY_cuoti + " INTEGER); ";
				
	}
	
	static class Gaoji extends BaseShiTi{
		public static final String TableName = GloableData.gaojiTableName;
		public static final String Create_table = "CREATE TABLE " + TableName
				+ "(locid INTEGER PRIMARY KEY AUTOINCREMENT, " 
				+ KEY_id + " INTEGER, "
				+ KEY_jibie + " TEXT, "
				+ KEY_type + " TEXT, "
				+ KEY_timu + " TEXT, "
				+ KEY_A + " TEXT, "
				+ KEY_B + " TEXT, "
				+ KEY_C + " TEXT, "
				+ KEY_D + " TEXT, "
				+ KEY_daan + " TEXT, "
				+ KEY_daan_desc + " TEXT, "
				+ KEY_tupian + " TEXT, "
				+ KEY_dui + " INTEGER, "
				+ KEY_cuo + " INTEGER, "
				+ KEY_cuoti + " INTEGER); ";
				
	}
	
	static class Gaojijishi extends BaseShiTi{
		public static final String TableName = GloableData.gaojijishiTableName;
		public static final String Create_table = "CREATE TABLE " + TableName
				+ "(locid INTEGER PRIMARY KEY AUTOINCREMENT, " 
				+ KEY_id + " INTEGER, "
				+ KEY_jibie + " TEXT, "
				+ KEY_type + " TEXT, "
				+ KEY_timu + " TEXT, "
				+ KEY_A + " TEXT, "
				+ KEY_B + " TEXT, "
				+ KEY_C + " TEXT, "
				+ KEY_D + " TEXT, "
				+ KEY_daan + " TEXT, "
				+ KEY_daan_desc + " TEXT, "
				+ KEY_tupian + " TEXT, "
				+ KEY_dui + " INTEGER, "
				+ KEY_cuo + " INTEGER, "
				+ KEY_cuoti + " INTEGER); ";
				
	}
	
	static class Jigong extends BaseShiTi{
		public static final String TableName = GloableData.jigongTableName;
		public static final String Create_table = "CREATE TABLE " + TableName
				+ "(locid INTEGER PRIMARY KEY AUTOINCREMENT, " 
				+ KEY_id + " INTEGER, "
				+ KEY_jibie + " TEXT, "
				+ KEY_type + " TEXT, "
				+ KEY_timu + " TEXT, "
				+ KEY_A + " TEXT, "
				+ KEY_B + " TEXT, "
				+ KEY_C + " TEXT, "
				+ KEY_D + " TEXT, "
				+ KEY_daan + " TEXT, "
				+ KEY_daan_desc + " TEXT, "
				+ KEY_tupian + " TEXT, "
				+ KEY_dui + " INTEGER, "
				+ KEY_cuo + " INTEGER, "
				+ KEY_cuoti + " INTEGER); ";
				
	}
	
	static class Zhongji extends BaseShiTi{
		public static final String TableName = GloableData.zhongjiTableName;
		public static final String Create_table = "CREATE TABLE " + TableName
				+ "(locid INTEGER PRIMARY KEY AUTOINCREMENT, " 
				+ KEY_id + " INTEGER, "
				+ KEY_jibie + " TEXT, "
				+ KEY_type + " TEXT, "
				+ KEY_timu + " TEXT, "
				+ KEY_A + " TEXT, "
				+ KEY_B + " TEXT, "
				+ KEY_C + " TEXT, "
				+ KEY_D + " TEXT, "
				+ KEY_daan + " TEXT, "
				+ KEY_daan_desc + " TEXT, "
				+ KEY_tupian + " TEXT, "
				+ KEY_dui + " INTEGER, "
				+ KEY_cuo + " INTEGER, "
				+ KEY_cuoti + " INTEGER); ";
				
	}
	
	static class LastReadInfo {
		public static final String TableName = GloableData.lastReadInfoTableName;
		public static final String KEY_id= "id",KEY_typeId = "typeId",KEY_localId = "localId";
		public static final String Create_table = "CREATE TABLE " + TableName
				+ "( " 
				+ KEY_id + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ KEY_typeId + " INTEGER, "
				+ KEY_localId + " INTEGER); ";
		public static final String[] columns = new String[] { KEY_id,KEY_typeId, 
			KEY_localId};
				
	}
	
	static class CuotiLastReadInfo {
		public static final String TableName = GloableData.cuotiLastReadInfoTableName;
		public static final String KEY_id= "id",KEY_typeId = "typeId",KEY_localId = "localId";
		public static final String Create_table = "CREATE TABLE " + TableName
				+ "( " 
				+ KEY_id + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ KEY_typeId + " INTEGER, "
				+ KEY_localId + " INTEGER); ";
		public static final String[] columns = new String[] { KEY_id,KEY_typeId, 
			KEY_localId};
				
	}
	
	private static final String DATABASE_NAME = "tikudb.db";
	private static final int DATABASE_VERSION = 5;
	private final Context context;
	private static DatabaseHelper DBHelper = null;
	private static SQLiteDatabase db = null;

	private static DBManager dbManager = null;

	private DBManager(Context ctx) {
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {
		public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		public void onCreate(SQLiteDatabase db) {
			db.execSQL(Chuji.Create_table);
			db.execSQL(Gaoji.Create_table);
			db.execSQL(Gaojijishi.Create_table);
			db.execSQL(Jigong.Create_table);
			db.execSQL(Zhongji.Create_table);
			db.execSQL(LastReadInfo.Create_table);
			db.execSQL(CuotiLastReadInfo.Create_table);
		}

		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//			db.execSQL("DROP TABLE IF EXISTS " + Chuji.TableName);
//			db.execSQL("DROP TABLE IF EXISTS " + Gaoji.TableName);
//			db.execSQL("DROP TABLE IF EXISTS " + Gaojijishi.TableName);
//			db.execSQL("DROP TABLE IF EXISTS " + Jigong.TableName);
//			db.execSQL("DROP TABLE IF EXISTS " + Zhongji.TableName);
//			db.execSQL("DROP TABLE IF EXISTS " + LastReadInfo.TableName);
//			onCreate(db);
			db.execSQL(CuotiLastReadInfo.Create_table);
		}
	}// DatabaseHelper

	/**
	 * 打开数据库
	 */
	public synchronized static DBManager open(Context ctx) throws SQLException {
		if (dbManager != null) {
			if (db == null) {
				db = DBHelper.getWritableDatabase();
			}
		} else {
			dbManager = new DBManager(ctx);
			db = DBHelper.getWritableDatabase();
		}
		return dbManager;
	}

	/**
	 * 关闭数据库
	 */
	public void close() {
		if (db != null) {
			db.close();
		}
		if (DBHelper != null) {
			DBHelper.close();
		}
		if (dbManager != null) {
			dbManager = null;
		}
	}
	
	public void beginTransaction(){
		db.beginTransaction();
	}
	
	public void setTransactionSuccessful() {
		db.setTransactionSuccessful();
	}
	
	public void endTransaction(){
		db.endTransaction();
	}
	// ///////////////////数据表操作///////////////////////////////
	/** 将制定的shitiBean插入到指定的表中*/
	public void insertOneTimu2DB(String tableName,ShiTiBean bean) {
		ContentValues cv = new ContentValues();
		cv.put(BaseShiTi.KEY_id, bean.id);
		cv.put(BaseShiTi.KEY_jibie, bean.jibie);
		cv.put(BaseShiTi.KEY_type, bean.timuType);
		cv.put(BaseShiTi.KEY_timu, bean.timu);
		cv.put(BaseShiTi.KEY_A, bean.optionA);
		cv.put(BaseShiTi.KEY_B, bean.optionB);
		cv.put(BaseShiTi.KEY_C, bean.optionC);
		cv.put(BaseShiTi.KEY_D, bean.optionD);
		cv.put(BaseShiTi.KEY_daan, bean.answerOption);
		cv.put(BaseShiTi.KEY_daan_desc, bean.answerDesc);
		cv.put(BaseShiTi.KEY_tupian, bean.picUrl);
		cv.put(BaseShiTi.KEY_dui, bean.duiCount);
		cv.put(BaseShiTi.KEY_cuo, bean.cuoCount);
		cv.put(BaseShiTi.KEY_cuoti, bean.isCuoti);
		db.insert(tableName, null, cv);
	}
	/** 获取tableName表startIndex处开始的pageCount条题目,timuType为题型，xuanze，panduan，quanbu*/
	public Cursor getOnePageTimuFromDB(String tableName,String timuType,boolean isCuoti,int startIndex,int pageCount) {
		String selection = null;
		String[] selectionArgs = null;
		if (!"quanbu".equals(timuType)||isCuoti) {//错题，或者具体的选择或判断题型
			if (!"quanbu".equals(timuType)&&isCuoti){
				selection = BaseShiTi.KEY_type+"=? AND "+BaseShiTi.KEY_cuoti+"=?";
				selectionArgs = new String[]{timuType,"1"};
			}else if (isCuoti) {//错题全部题型
				selection = BaseShiTi.KEY_cuoti+"=?";
				selectionArgs = new String[]{"1"};
			}else if (!"quanbu".equals(timuType)) {//非错题，选择题或判断题
				selection = BaseShiTi.KEY_type+"=?";
				selectionArgs = new String[]{timuType};
			}
		}
		return db.query(tableName, BaseShiTi.columns, selection, selectionArgs, null, null, null, startIndex+","+pageCount);
	}
	
	/** 获取tableName表startIndex处开始的pageCount条题目,timuType为题型，xuanze，panduan，quanbu*/
	public Cursor getOnePageTimuFromDBNew(String tableName,String timuType,boolean isCuoti) {
		String selection = null;
		String[] selectionArgs = null;
		if (!"quanbu".equals(timuType)||isCuoti) {//错题，或者具体的选择或判断题型
			if (!"quanbu".equals(timuType)&&isCuoti){
				selection = BaseShiTi.KEY_type+"=? AND "+BaseShiTi.KEY_cuoti+"=?";
				selectionArgs = new String[]{timuType,"1"};
			}else if (isCuoti) {//错题全部题型
				selection = BaseShiTi.KEY_cuoti+"=?";
				selectionArgs = new String[]{"1"};
			}else if (!"quanbu".equals(timuType)) {//非错题，选择题或判断题
				selection = BaseShiTi.KEY_type+"=?";
				selectionArgs = new String[]{timuType};
			}
		}
		return db.query(tableName, BaseShiTi.columns, selection, selectionArgs, null, null, null);
	}
	
	public Cursor getOnePageTimuFromDBByLastRead(String tableName,String timuType,boolean isCuoti,int pageCount) {
		String selection = null;
		String[] selectionArgs = null;
		if (!"quanbu".equals(timuType)||isCuoti) {//错题，或者具体的选择或判断题型
			if (!"quanbu".equals(timuType)&&isCuoti){
				selection = BaseShiTi.KEY_type+"=? AND "+BaseShiTi.KEY_cuoti+"=?";
				selectionArgs = new String[]{timuType,"1"};
			}else if (isCuoti) {//错题全部题型
				selection = BaseShiTi.KEY_cuoti+"=?";
				selectionArgs = new String[]{"1"};
			}else if (!"quanbu".equals(timuType)) {//非错题，选择题或判断题
				selection = BaseShiTi.KEY_type+"=?";
				selectionArgs = new String[]{timuType};
			}
		}
		return db.query(tableName, BaseShiTi.columns, selection, selectionArgs, null, null, null, "0,"+pageCount);
	}
	public Cursor getOnePageTimuFromDBByLastReadNew(String tableName,String timuType,boolean isCuoti) {
		String selection = null;
		String[] selectionArgs = null;
		if (!"quanbu".equals(timuType)||isCuoti) {//错题，或者具体的选择或判断题型
			if (!"quanbu".equals(timuType)&&isCuoti){
				selection = BaseShiTi.KEY_type+"=? AND "+BaseShiTi.KEY_cuoti+"=?";
				selectionArgs = new String[]{timuType,"1"};
			}else if (isCuoti) {//错题全部题型
				selection = BaseShiTi.KEY_cuoti+"=?";
				selectionArgs = new String[]{"1"};
			}else if (!"quanbu".equals(timuType)) {//非错题，选择题或判断题
				selection = BaseShiTi.KEY_type+"=?";
				selectionArgs = new String[]{timuType};
			}
		}
		return db.query(tableName, BaseShiTi.columns, selection, selectionArgs, null, null, null);
	}
	/**
	 * 获取某张表数据总数
	 * @param tableName
	 * @return count，-1表示没有查到数据
	 */
	public int getCountFromDB(String tableName,String timuType,boolean isCuoti) {
		String selection = null;
		String[] selectionArgs = null;
		if (!"quanbu".equals(timuType)||isCuoti) {//错题，或者具体的选择或判断题型
			if (!"quanbu".equals(timuType)&&isCuoti){
				selection = BaseShiTi.KEY_type+"=? AND "+BaseShiTi.KEY_cuoti+"=?";
				selectionArgs = new String[]{timuType,"1"};
			}else if (isCuoti) {//错题全部题型
				selection = BaseShiTi.KEY_cuoti+"=?";
				selectionArgs = new String[]{"1"};
			}else if (!"quanbu".equals(timuType)) {//非错题，选择题或判断题
				selection = BaseShiTi.KEY_type+"=?";
				selectionArgs = new String[]{timuType};
			}
		}
		Cursor cur = db.query(tableName, BaseShiTi.columns, selection, selectionArgs, null, null,null);
		if (cur!=null) {
			if (cur.moveToFirst()) {
				return cur.getCount();
			}
			cur.close();
		}
		return -1;
	}
	
	public int getPanduanCount(String tableName) {
		int count = -1;
		Cursor cur = db.query(tableName, BaseShiTi.columns, BaseShiTi.KEY_type+"=?",
				new String[]{"panduan"}, null, null, null);
		if (cur!=null){
			if (cur.moveToFirst()){
				count = cur.getCount();
			}
			cur.close();
		}
		return count;
	}
	
	public void updateByIsCuoti(String tableName,int id,int isCuoti) {
		ContentValues cv = new ContentValues();
		cv.put(BaseShiTi.KEY_cuoti, isCuoti);
		db.update(tableName, cv, BaseShiTi.KEY_id+"=?", new String[]{String.valueOf(id)});
	}
	
	public void updateByDuiCount(String tableName,int id,int duiCount){
		ContentValues cv = new ContentValues();
		cv.put(BaseShiTi.KEY_dui, duiCount);
		db.update(tableName, cv, BaseShiTi.KEY_id+"=?", new String[]{String.valueOf(id)});
	}
	
	public void updateByCuoCount(String tableName,int id,int cuoCount){
		ContentValues cv = new ContentValues();
		cv.put(BaseShiTi.KEY_cuo, cuoCount);
		db.update(tableName, cv, BaseShiTi.KEY_id+"=?", new String[]{String.valueOf(id)});
	}
	/////////////////////历史记录表操作/////////////////////////////
	public void insert2LastReadInfo(LastReadInfoBean bean) {
		ContentValues cv = new ContentValues();
		cv.put(LastReadInfo.KEY_typeId, bean.typeId);
		cv.put(LastReadInfo.KEY_localId, bean.localId);
		UtilsManager.println("更新数据库");
		int result = db.update(LastReadInfo.TableName, cv, LastReadInfo.KEY_typeId+"=?", new String[]{String.valueOf(bean.typeId)});
		if (result<=0) {
			UtilsManager.println("数据库不存在，插入数据库");
			db.insert(LastReadInfo.TableName, null, cv);
		}
	}
	
	public void deleteOneLastReadInfo(int typeId) {
		db.delete(LastReadInfo.TableName, LastReadInfo.KEY_typeId+"=?", new String[]{String.valueOf(typeId)});
	}
	
	public void update2LastReadInfo(int id,int localId) {
		ContentValues cv = new ContentValues();
		cv.put(LastReadInfo.KEY_localId, localId);
		db.update(LastReadInfo.TableName, cv, LastReadInfo.KEY_id+"=?", new String[]{String.valueOf(id)});
	}
	/**返回-1表示数据库无符合要求的数据，既没有历史记录*/
	public int getDataIdFromLastReadInfo(int typeId) {
		Cursor cur = db.query(LastReadInfo.TableName, LastReadInfo.columns,
				LastReadInfo.KEY_typeId+"=? AND "+LastReadInfo.KEY_localId+">?",
				new String[]{String.valueOf(typeId),"1"}, null, null, null);
		int result = -1;
		if (cur!=null) {
			if (cur.moveToFirst()) {
				result = cur.getInt(2);
			}
			cur.close();
		}
		return result;
	}
	/////////////////////历史记录表操作/////////////////////////////
	/////////////////////错题历史记录表操作/////////////////////////////
	public void insert2CuotiLastReadInfo(LastReadInfoBean bean) {
		ContentValues cv = new ContentValues();
		cv.put(CuotiLastReadInfo.KEY_typeId, bean.typeId);
		cv.put(CuotiLastReadInfo.KEY_localId, bean.localId);
		UtilsManager.println("更新数据库");
		int result = db.update(CuotiLastReadInfo.TableName, cv, CuotiLastReadInfo.KEY_typeId+"=?", new String[]{String.valueOf(bean.typeId)});
		if (result<=0) {
			UtilsManager.println("数据库不存在，插入数据库");
			db.insert(CuotiLastReadInfo.TableName, null, cv);
		}
	}
	
	public void deleteOneCuotiLastReadInfo(int typeId) {
		db.delete(CuotiLastReadInfo.TableName, CuotiLastReadInfo.KEY_typeId+"=?", new String[]{String.valueOf(typeId)});
	}
	
	public void update2CuotiLastReadInfo(int id,int localId) {
		ContentValues cv = new ContentValues();
		cv.put(CuotiLastReadInfo.KEY_localId, localId);
		db.update(CuotiLastReadInfo.TableName, cv, CuotiLastReadInfo.KEY_id+"=?", new String[]{String.valueOf(id)});
	}
	/**返回-1表示数据库无符合要求的数据，既没有错题历史记录*/
	public int getDataIdFromCuotiLastReadInfo(int typeId) {
		Cursor cur = db.query(CuotiLastReadInfo.TableName, CuotiLastReadInfo.columns,
				CuotiLastReadInfo.KEY_typeId+"=? AND "+CuotiLastReadInfo.KEY_localId+">?",
				new String[]{String.valueOf(typeId),"1"}, null, null, null);
		int result = -1;
		if (cur!=null) {
			if (cur.moveToFirst()) {
				result = cur.getInt(2);
			}
			cur.close();
		}
		return result;
	}
	/////////////////////历史记录表操作/////////////////////////////
}

